﻿-- Function: pl_function.ver_predio(character varying, character varying, character varying, refcursor)

-- DROP FUNCTION pl_function.ver_predio(character varying, character varying, character varying, refcursor);

CREATE OR REPLACE FUNCTION pl_function.ver_predio(p_mpredio character varying, p_mhresum character varying, p_cperiod character varying, p_ref refcursor)
  RETURNS refcursor AS
$BODY$
begin
	open p_ref for select
		  a.mpropie, a.mpredio, a.ctippro, a.vtippro
		, to_char(a.dfecadq, 'dd/MM/yyyy') dfecadq
		, case when to_char(a.dfecdes, 'dd/MM/yyyy')='01/01/1900' then '' else to_char(a.dfecdes, 'dd/MM/yyyy') end dfecdes
		, a.vnrodoc
		, to_char(a.dfecdoc, 'dd/MM/yyyy') dfecdoc
		, a.cmotivo, a.vmotivo, a.ctipdat, a.vtipfic
		, a.ctipdoc, a.vtipdoc, a.tnompob, a.tnomvia
		, a.dnumero, a.dinteri, a.dletras, a.ddepart
		, a.destaci, a.ddeposi, a.drefere, a.dmanzan
		, a.dnlotes, a.ccatast, a.cplanos, a.ctipmer
		, a.dnummer, a.cdiscat, a.czoncat, a.cmzacat
		, a.cseccat, a.cltecat, a.cundcat, a.dbloque
		, a.dseccio, a.dunidad, a.vdirpre, a.nestado
		, right(a.ccodpre, 10) ccodpre, a.cperiod, a.idanexo, a.ccodcuc
		, a.vtippre, a.dpredio, a.ctippre, a.cclasif
		, a.ccondic, a.cestado, a.cusogen, a.cusoesp
		, to_char(a.nporcen, 'FM9990.00') nporcen
		, to_char(a.ntertot, 'FM999999990.00') ntertot
		, to_char(a.nporter, 'FM999999990.00') nporter
		, to_char(a.nterren, 'FM999999990.00') nterren
		, to_char(a.ncomtot, 'FM999999990.00') ncomtot
		, to_char(a.nporcom, 'FM999999990.00') nporcom
		, to_char(a.narecom, 'FM999999990.00') narecom
		, to_char(a.narance, 'FM999999990.00') narance
		, to_char(a.nvalpis, 'FM999,999,990.00') nvalpis
		, to_char(a.nvalins, 'FM999,999,990.00') nvalins
		, to_char(a.nvalter, 'FM999,999,990.00') nvalter
		, to_char(a.nvalpre, 'FM999,999,990.00') nvalpre
		, to_char(a.nporafe, 'FM990.00') nporafe
		, to_char(a.nvalafe, 'FM999,999,990.00') nvalafe
		, to_char(a.dafecta, 'dd/MM/yyyy HH:MI:SS') dafecta
		, to_char(a.nfrente, 'FM9999990.00') nfrente
		, a.ncanper, a.vnrodjj, a.ctippar, a.vobserv
		, a.destado, a.mperson, a.vusogen, a.vusoesp
		, a.vtippar, a.vusernm
		, to_char(a.ddatetm, 'dd/MM/yyyy HH:MI:SS') ddatetm
		, to_char(a.dfectra, 'dd/MM/yyyy HH:MI:SS') dfectra
		, a.cnotari, a.ctiptra, a.cpartid, a.csubtip
		, a.cmotadq, a.mhresum
		, to_char(case when nbarrid is null then 0 else nbarrid end , 'FM999,999,990.00') nbarrid
		, to_char(case when nparque is null then 0 else nparque end , 'FM999,999,990.00') nparque
		, to_char(case when nrecole is null then 0 else nrecole end , 'FM999,999,990.00') nrecole
		, to_char(case when nserena is null then 0 else nserena end , 'FM999,999,990.00') nserena
		, left(a.ccodpre, 10) ccodpre2,a.nrecalc
	from registro.vwpredio a 
	where a.mpredio=p_mpredio
		and (p_mhresum = '-1' or a.mhresum=p_mhresum)
		and (p_cperiod = '-1' or a.cperiod = (
			select max(case when lower(p_cperiod)='max' then c.cperiod else p_cperiod end) 
			from registro.dpredio b inner join registro.vpredio c on b.idsigma=c.dpredio 
			where b.mpredio=p_mpredio 
				and (p_mhresum = '-1' or b.mhresum=p_mhresum))
		);
	return p_ref;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pl_function.ver_predio(character varying, character varying, character varying, refcursor)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.ver_predio(character varying, character varying, character varying, refcursor) TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.ver_predio(character varying, character varying, character varying, refcursor) TO public;
GRANT EXECUTE ON FUNCTION pl_function.ver_predio(character varying, character varying, character varying, refcursor) TO desarrollo;
